SQL Server must be configured to allow authorized users to capture, record, and log all content related to a user session.

Overview

Finding ID	Version	Rule ID	IA Controls	Severity
V-79143	SQL6-D0-004800	SV-93849r1_rule		Medium

Description
Without the capability to capture, record, and log all content related to a user session, investigations into suspicious user activity would be hampered. Typically, this DBMS capability would be used in conjunction with comparable monitoring of a user's online session, involving other software components such as operating systems, web servers and front-end user applications. The current requirement, however, deals specifically with SQL Server.

Description

Without the capability to capture, record, and log all content related to a user session, investigations into suspicious user activity would be hampered. Typically, this DBMS capability would be used in conjunction with comparable monitoring of a user's online session, involving other software components such as operating systems, web servers and front-end user applications. The current requirement, however, deals specifically with SQL Server.

STIG	Date
MS SQL Server 2016 Instance Security Technical Implementation Guide	2018-03-09

Details

Check Text ( C-78735r1_chk )
Review the system documentation to determine if auditing of a session is required. If auditing of a session is not required, this is not a finding. If the documentation does not exist, this is a finding.

Fix Text (F-85895r1_fix)
SQL Server Extended Events has a highly scalable and highly configurable architecture that allows users to collect as much or as little information as is necessary to troubleshoot or document session activity. Extended Events is a light weight performance monitoring system that uses very few performance resources. Extended Events provides two graphical user interfaces and T-SQL syntax to create, modify, display, and analyze session data. Use Extended Events to capture and review a user session. Auditing must only be enabled for member of the auditors group or sysadmin. To create, alter, or drop a server audit, principals require the ALTER ANY SERVER AUDIT or the CONTROL SERVER permission. To view an Audit log requires the CONTROL SERVER permission. Create a server role specifically for audit maintainers and give it permission to maintain audits without granting it unnecessary permissions (the role name used here is an example; other names may be used): CREATE SERVER ROLE SERVER_AUDIT_MAINTAINERS; GO GRANT ALTER ANY SERVER AUDIT TO SERVER_AUDIT_MAINTAINERS; GO Use REVOKE and/or DENY and/or ALTER SERVER ROLE ... DROP MEMBER ... statements to remove the ALTER ANY SERVER AUDIT permission from all logins. Then, for each authorized login, run the statement: ALTER SERVER ROLE SERVER_AUDIT_MAINTAINERS ADD MEMBER; GO Use REVOKE and/or DENY and/or ALTER SERVER ROLE ... DROP MEMBER ... statements to remove CONTROL SERVER, ALTER ANY DATABASE and CREATE ANY DATABASE permissions from logins that do not need them.

Fix Text (F-85895r1_fix)

SQL Server Extended Events has a highly scalable and highly configurable architecture that allows users to collect as much or as little information as is necessary to troubleshoot or document session activity.

Extended Events is a light weight performance monitoring system that uses very few performance resources. Extended Events provides two graphical user interfaces and T-SQL syntax to create, modify, display, and analyze session data. Use Extended Events to capture and review a user session. Auditing must only be enabled for member of the auditors group or sysadmin. To create, alter, or drop a server audit, principals require the ALTER ANY SERVER AUDIT or the CONTROL SERVER permission. To view an Audit log requires the CONTROL SERVER permission.

Create a server role specifically for audit maintainers and give it permission to maintain audits without granting it unnecessary permissions (the role name used here is an example; other names may be used):
CREATE SERVER ROLE SERVER_AUDIT_MAINTAINERS;
GO
GRANT ALTER ANY SERVER AUDIT TO SERVER_AUDIT_MAINTAINERS;
GO

Use REVOKE and/or DENY and/or ALTER SERVER ROLE ... DROP MEMBER ... statements to remove the ALTER ANY SERVER AUDIT permission from all logins. Then, for each authorized login, run the statement:
ALTER SERVER ROLE SERVER_AUDIT_MAINTAINERS ADD MEMBER;
GO

Use REVOKE and/or DENY and/or ALTER SERVER ROLE ... DROP MEMBER ... statements to remove CONTROL SERVER, ALTER ANY DATABASE and CREATE ANY DATABASE permissions from logins that do not need them.